package com.jiepos.mpos.core.dao;

import java.io.Serializable;
import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.ArrayUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.jiepos.mpos.common.constant.SystemConstant;
import com.jiepos.mpos.core.entity.Pagination;
import com.jiepos.mpos.core.util.StringUtils;

import oracle.jdbc.driver.OracleTypes;

@Repository
public class BaseDao<T> {
	/** 自己的日志 */
	private final Logger currLog = LoggerFactory.getLogger(BaseDao.class);

	/** 子类的日志 */
	public final Logger log = LoggerFactory.getLogger(getClass());
	/**
	 * 实体类类型(由构造方法自动赋值)
	 */
	private Class<T> entityClass;
	/**
	 * 数据库操作模版
	 */
	@Autowired
	protected JdbcTemplate jdbcTemplate;
	/** 设置一些操作的常量 */
	public static final String SQL_INSERT = "insert";
	public static final String SQL_UPDATE = "update";
	public static final String SQL_DELETE = "delete";

	@SuppressWarnings("unchecked")
	public BaseDao() {
		Class<?> c = getClass();
		Type t = c.getGenericSuperclass();
		if (t instanceof ParameterizedType) {
			Type[] p = ((ParameterizedType) t).getActualTypeArguments();
			this.entityClass = (Class<T>) p[0];
		}
		// currLog.info("Dao实现类是：" + entityClass.getName());
	}

	/**
	 * 保存实体
	 * 
	 * @param entity
	 */
	public void save(T entity) {
		String sql = this.makeSql(SQL_INSERT,null);
		log.debug("save sql==" + sql);

		Object[] args = this.setArgs(entity, SQL_INSERT,null);
		Object[] args2 = new Object[args.length];

		int[] argTypes = this.setArgTypes(entity, SQL_INSERT,null);
		int[] argTypes2 = new int[argTypes.length];

 		if(StringUtils.isBlank(String.valueOf(args[args.length-1]))){
			argTypes2 = new int[argTypes.length-1];
			args2 = new Object[args.length-1];
		} 
		for(int i = 0 ;i < argTypes2.length ;i++){
			args2[i] = args[i];
			argTypes2[i] = argTypes[i];
		//	log.debug("arg="+args2[i] +",");
		//	log.debug("type="+argTypes2[i] +",");
		}

		jdbcTemplate.update(sql.toString(), args2, argTypes2);
	}

	/**
	 * 更新实体
	 * 
	 * @param entity
	 */
	public void update(T entity,List noUpdateDataList) {
		String sql = this.makeSql(SQL_UPDATE,noUpdateDataList);
		log.debug("update sql : " + sql);
		Object[] args = this.setArgs(entity, SQL_UPDATE,noUpdateDataList);
		int[] argTypes = this.setArgTypes(entity, SQL_UPDATE,noUpdateDataList);
		jdbcTemplate.update(sql, args, argTypes);
	}

	/**
	 * 删除实体
	 * 
	 * @param entity
	 */
	@Deprecated
	public void delete(T entity) {
		String sql = this.makeSql(SQL_DELETE,null);
		Object[] args = this.setArgs(entity, SQL_DELETE,null);
		int[] argTypes = this.setArgTypes(entity, SQL_DELETE,null);
		jdbcTemplate.update(sql, args, argTypes);
	}

	/**
	 * 根据主键删除数据
	 * 
	 * @param id
	 */
	public void delete(Serializable id) {
		String sql = " DELETE FROM " + SystemConstant.TBL
				+ entityClass.getSimpleName().replace(SystemConstant.TBL_SUFFIX, "") + " WHERE rec_id=?";
		jdbcTemplate.update(sql, id);
	}

	/**
	 * 根据sql修改数据
	 * 
	 * @param sql
	 */
	@Deprecated
	public void update(String sql) {
		log.debug("update sql : " + sql);
		jdbcTemplate.update(sql);
	}
	
	/**
	 * 根据sql修改数据
	 * 
	 * @param sql
	 */
	public void update(String sql,List paramList) {
		log.debug("update sql : " + sql);
		if(paramList == null || paramList.size() == 0){
			jdbcTemplate.update(sql);
		}else{
			Object[] tempObj = paramList.toArray();	
			jdbcTemplate.update(sql,tempObj);
		}
		
	}

	/**
	 * 更新多条数据状态
	 * 
	 * @param ids
	 *            id集合
	 */
	public void deleteByStatus(String idsStr) {
		final String[] ids = idsStr.split(",");
		String sql = " UPDATE " + SystemConstant.TBL
				+ entityClass.getSimpleName().replace(SystemConstant.TBL_SUFFIX, "") + " SET REC_ST='"
				+ SystemConstant.DEL_STATUS + "' , REC_UPD_TS=SYSTIMESTAMP , last_oper_in='" + SystemConstant.OPER_DEL
				+ "' WHERE rec_id = ?";
		log.debug("deleteByStatus sql : " + sql);
		log.debug("delete by status ids: " + ids);
		jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {

			@Override
			public int getBatchSize() {
				return ids.length;
			}

			@Override
			public void setValues(PreparedStatement arg0, int arg1) throws SQLException {
				arg0.setString(1, ids[arg1]);
			}

		});
	}

	/**
	 * 根据参数更新多条数据状态
	 * 
	 * @param ids
	 *            id集合
	 */
	public void deleteByStatus(String param, String idsStr) {
		final String[] ids = idsStr.split(",");
		String sql = " UPDATE " + SystemConstant.TBL
				+ entityClass.getSimpleName().replace(SystemConstant.TBL_SUFFIX, "") + " SET REC_ST='"
				+ SystemConstant.DEL_STATUS + "' , REC_UPD_TS=SYSTIMESTAMP , last_oper_in='" + SystemConstant.OPER_DEL
				+ "' WHERE " + param + " = ?";
		log.debug("deleteByStatus sql : " + sql);
		log.debug("delete by status ids: " + ids);
		jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {

			@Override
			public int getBatchSize() {
				return ids.length;
			}

			@Override
			public void setValues(PreparedStatement arg0, int arg1) throws SQLException {
				arg0.setString(1, ids[arg1]);
			}

		});
	}
	
	/**
	 * 根据2个参数更新多条数据状态
	 * 
	 * @param ids
	 *            id集合
	 */
	public void deleteByParam(String param,String param2, String idsStr) {
		final String[] ids = idsStr.split(",");
		String sql = " UPDATE " + SystemConstant.TBL
				+ entityClass.getSimpleName().replace(SystemConstant.TBL_SUFFIX, "") + " SET REC_ST='"
				+ SystemConstant.DEL_STATUS + "' , REC_UPD_TS=SYSTIMESTAMP , last_oper_in='" + SystemConstant.OPER_DEL
				+ "' WHERE " + param + " = ? and "+ param2 +" = ?";
		log.debug("deleteByStatus sql : " + sql);
		log.debug("delete by status ids: " + ids);
		jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {

			@Override
			public int getBatchSize() {
				return ids.length;
			}

			@Override
			public void setValues(PreparedStatement arg0, int arg1) throws SQLException {
				arg0.setString(1, ids[arg1]);
				arg0.setString(2, ids[1]);
			}

		});
	}
	/**
	 * 根据多个参数删除记录
	 * @param param
	 * @param param2
	 * @param param3
	 * @param idsStr
	 */
	public void deleteByParams(String param,String param2,String param3, String idsStr) {
		final String[] ids =idsStr.split(",");
		String sql = " UPDATE " + SystemConstant.TBL
				+ entityClass.getSimpleName().replace(SystemConstant.TBL_SUFFIX, "") + " SET REC_ST='"
				+ SystemConstant.DEL_STATUS + "' , REC_UPD_TS=SYSTIMESTAMP , last_oper_in='" + SystemConstant.OPER_DEL
				+ "' WHERE " + param + " = ? and "+ param2 +" = ? and "+ param3 +" = ?" ;
		log.debug("deleteByStatus sql : " + sql);
		log.debug("delete by status ids: " + ids);
		jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {

			@Override
			public int getBatchSize() {
				return ids.length;
			}

			@Override
			public void setValues(PreparedStatement arg0, int arg1) throws SQLException {
				arg0.setString(1, ids[arg1]);
				arg0.setString(2, ids[1]);
				arg0.setString(3, ids[2]);
			}

		});
	}
	/**
	 * 更新多条数据状态
	 * 
	 * @param ids
	 *            id集合
	 */
	public void deleteByStatusParam(String param, String idsStr, String loginName) {
		final String[] ids =idsStr.split(",");
		String sql = " UPDATE " + SystemConstant.TBL
				+ entityClass.getSimpleName().replace(SystemConstant.TBL_SUFFIX, "") + " SET REC_ST='"
				+ SystemConstant.DEL_STATUS + "' ,REC_UPD_TS=SYSTIMESTAMP,LAST_OPER_IN='" + SystemConstant.OPER_DEL
				+ "' ,REC_UPD_USR_ID=" + StringUtils.toSqlStringValue(loginName) + " WHERE " + param + " = ?";
		log.debug("loginName=" + loginName + " deleteByStatus sql : " + sql);
		log.debug("loginName=" + loginName + " delete by status ids: " + ids);
		jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {

			@Override
			public int getBatchSize() {
				return ids.length;
			}

			@Override
			public void setValues(PreparedStatement arg0, int arg1) throws SQLException {
				arg0.setString(1, ids[arg1]);
			}

		});
	}

	/**
	 * 删除所有的数据
	 */
	public void deleteAll() {
		String sql = " TRUNCATE TABLE " + SystemConstant.TBL
				+ entityClass.getSimpleName().replace(SystemConstant.TBL_SUFFIX, "");
		jdbcTemplate.execute(sql);
	}

	/**
	 * 根据主键查询数据
	 * 
	 * @param id
	 * @return
	 */
	public T findById(Serializable id) {
		String sql = "SELECT * FROM " + SystemConstant.TBL
				+ entityClass.getSimpleName().replace(SystemConstant.TBL_SUFFIX, "") + " WHERE rec_id=?";
		RowMapper<T> rowMapper = BeanPropertyRowMapper.newInstance(entityClass);
		List<T> list = jdbcTemplate.query(sql, rowMapper, id);
		if (list != null && list.size() > 0) {
			return list.get(0);
		}
		return null;
	}

	/**
	 * 根据id查询创建时间
	 * 
	 * @param id
	 * @return
	 */
	public Object findCreateTsById(String id) {
		String sql = "select rec_crt_ts from " + SystemConstant.TBL
				+ entityClass.getSimpleName().replace(SystemConstant.TBL_SUFFIX, "") + " WHERE rec_id=?";
		List<Map<String, Object>> mapList = jdbcTemplate.queryForList(sql, id);
		if (mapList != null && mapList.size() > 0) {
			return mapList.get(0).get("rec_crt_ts");
		}
		return null;
	}

	/**
	 * 根据参数查询数据
	 * 
	 * @param id
	 * @return
	 */
	public T findByParam(String param, String value) {
		List<T> list = findListByParam(param, value);
		if (list != null && list.size() > 0) {
			return list.get(0);
		}
		return null;
	}
	
	public List<T> findListByParam(String param, String value) {
		if(StringUtils.isBlank(value)){
			return null;
		}
		value = value.replace("'", "");
		String tempUpper = "";
	    
		if(!StringUtils.isChinese(value)){
			tempUpper = "UPPER";
		}
		String sql = "SELECT * FROM " + SystemConstant.TBL
				+ entityClass.getSimpleName().replace(SystemConstant.TBL_SUFFIX, "") + " WHERE "+tempUpper+"(" + param + ")="+tempUpper+"(?) and rec_st!='0'";
		log.debug(sql);
		RowMapper<T> rowMapper = BeanPropertyRowMapper.newInstance(entityClass);
		List<T> list = jdbcTemplate.query(sql, rowMapper,value);
		return list;
	}
	
	
	/**
	 * 根据参数查询数据
	 * 
	 * @param id
	 * @return
	 */
	public T findByParamNoRecSt(String param, String value) {
		if(StringUtils.isBlank(value)){
			return null;
		}
		value = value.replace("'", "");
		String tempUpper = "";
	    
		if(!StringUtils.isChinese(value)){
			tempUpper = "UPPER";
		}
		String sql = "SELECT * FROM " + SystemConstant.TBL
				+ entityClass.getSimpleName().replace(SystemConstant.TBL_SUFFIX, "") + " WHERE "+tempUpper+"(" + param + ")="+tempUpper+"(?)  ";
		RowMapper<T> rowMapper = BeanPropertyRowMapper.newInstance(entityClass);
		List<T> list = jdbcTemplate.query(sql, rowMapper,value);
		log.debug(sql);
		if (list != null && list.size() > 0) {
			return list.get(0);
		}
		return null;
	}
	/**
	 * 根据参数查询数据
	 * 
	 * @param id
	 * @return
	 */
	public T findByParam2(String param, String value) {
		if(StringUtils.isBlank(value)){
			return null;
		}
		value = value.replace("'", "");
		String tempUpper = "";
	    
		if(!StringUtils.isChinese(value)){
			tempUpper = "UPPER";
		}
		String sql = "SELECT * FROM " + SystemConstant.TBL
				+ entityClass.getSimpleName().replace(SystemConstant.TBL_SUFFIX, "") + " WHERE "+tempUpper+"(" + param + ")="+tempUpper+"(?) ";
		RowMapper<T> rowMapper = BeanPropertyRowMapper.newInstance(entityClass);
		List<T> list = jdbcTemplate.query(sql, rowMapper,value);
		log.debug(sql);
		if (list != null && list.size() > 0) {
			return list.get(0);
		}
		return null;
	}
	/**
	 * 根据多个参数查询条件
	 * @param param
	 * @param value
	 * @return
	 */
	public T findByParams(Object[] param1, Object[] param2) {
		if(param2 == null){
			return null;
		}
		Object obj1 = param1[0];
		Object obj2 = param1[1];
		String value1 = String.valueOf(obj1);
		String value2 = String.valueOf(obj2);
		String tempUpper = "";
		tempUpper = "UPPER";
		String sql = "SELECT * FROM " + SystemConstant.TBL
				+ entityClass.getSimpleName().replace(SystemConstant.TBL_SUFFIX, "") + " WHERE "+tempUpper+"(" + value1 + ")="+tempUpper+"(?) "+" AND "+tempUpper+"(" + value2 + ")="+tempUpper+"(?) AND REC_ST!='0' ";
		log.debug(sql);
		RowMapper<T> rowMapper = BeanPropertyRowMapper.newInstance(entityClass);
		List<T> list = jdbcTemplate.query(sql, rowMapper,param2);
		
		if (list != null && list.size() > 0) {
			return list.get(0);
		}
		return null;
	}
	public T findByParamses(Object[] param1, Object[] param2) {
		if(param2 == null){
			return null;
		}
		Object obj1 = param1[0];
		Object obj2 = param1[1];
		Object obj3 = param1[2];
		String value1 = String.valueOf(obj1);
		String value2 = String.valueOf(obj2);
		String value3 = String.valueOf(obj3);
		String tempUpper = "";
		tempUpper = "UPPER";
		String sql = "SELECT * FROM " + SystemConstant.TBL
				+ entityClass.getSimpleName().replace(SystemConstant.TBL_SUFFIX, "") + " WHERE "+tempUpper+"(" + value1 + ")="+tempUpper+"(?) "+" AND "+tempUpper+"(" + value2 + ")="+tempUpper+"(?)"+" AND "+tempUpper+"(" + value3 + ")="+tempUpper+"(?)"+" AND REC_ST!='0' ";
		log.debug(sql);
		RowMapper<T> rowMapper = BeanPropertyRowMapper.newInstance(entityClass);
		List<T> list = jdbcTemplate.query(sql, rowMapper,param2);
		
		if (list != null && list.size() > 0) {
			return list.get(0);
		}
		return null;
	}
	/**
	 * 查询所有的数据
	 * 
	 * @return
	 */
	public List<T> findAll() {
		String sql = "SELECT * FROM " + SystemConstant.TBL
				+ entityClass.getSimpleName().replace(SystemConstant.TBL_SUFFIX, "");
		RowMapper<T> rowMapper = BeanPropertyRowMapper.newInstance(entityClass);
		return jdbcTemplate.query(sql, rowMapper);
	}
	
	/**
	 * 根据条件查询列表
	 * @param sql
	 * @param args
	 * @return
	 */
	public List<T> queryForListByParam(String sql, Object... args) {
		log.debug("queryForListByParam sql : " + sql);
		RowMapper<T> rowMapper = BeanPropertyRowMapper.newInstance(entityClass);
		return this.jdbcTemplate.query(sql, rowMapper, args);
	}
	
	/**
	 * 根据list拼装pagination
	 * @param pagination
	 * @param paramList
	 * @return
	 */
	public Pagination getPaginationByList(Pagination pagination,List<Map> paramList){
		// 当前页
		final int currPage = pagination.page;
		// 每页显示数
		final int rowsPerPage = pagination.rows;
		// 当前索引 = 当前页 * 每页显示数
		//int curIndex = (currPage - 1) * rowsPerPage;
		Map map = null;
		List<Object[]> dataList = new ArrayList<Object[]>();
		if(currPage * rowsPerPage > paramList.size()){
			for(int curIndex = (currPage - 1) * rowsPerPage;curIndex < paramList.size();curIndex++){
				map = paramList.get(curIndex);
				Object[] obj = (Object[]) map.values().toArray();
				dataList.add(obj);
			}
		}else{
			for(int curIndex = (currPage - 1) * rowsPerPage; curIndex < currPage * rowsPerPage; curIndex++) {
				map = paramList.get(curIndex);
				Object[] obj = (Object[]) map.values().toArray();
				dataList.add(obj);
			}
		}
		pagination.maxRowCount = paramList.size();
		pagination.page = currPage;
		pagination.setData(dataList);
		return pagination;
	}
	public Pagination getPagination(Pagination pagination, String prefix, List paramList) {
		// where 与 order by 要写在select * from table 的后面，而不是where rownum<=? )
		// where rn>=?的后面
		// 当前页
		final int currPage = pagination.page;
		// 每页显示数
		final int rowsPerPage = pagination.rows;
		// 当前索引 = 当前页 * 每页显示数
		final int curIndex = (currPage - 1) * rowsPerPage;
		StringBuffer sql = new StringBuffer(" SELECT * FROM (SELECT t.*,ROWNUM rn FROM ( ");

		if (!StringUtils.isBlank(pagination.getSort())) {
			if (pagination.queryString.contains("order by")) {
				int start = pagination.queryString.indexOf("order by");
				pagination.queryString = pagination.queryString.substring(0, start);
			}
			sql.append(pagination.queryString);
			String temp = " order by ";
			if (sql.indexOf("order by") > -1) {
				temp = "";
			}
			sql.append(temp + "  " + prefix + "." + pagination.getSort() + " " + pagination.getOrder());
		} else {
			sql.append(pagination.queryString);
		}
		sql.append(" ) t WHERE ROWNUM<=? ) WHERE rn>?");
		Object[] args = null;
		if (paramList == null || paramList.size() == 0) {			 
			args = new Object[2];
			args[0] = curIndex + rowsPerPage;
			args[1] = curIndex;
			// args = { curIndex + rowsPerPage, curIndex };
		} else {
			Object[] tempObj = paramList.toArray();
			args = new Object[tempObj.length + 2];
			// args = Arrays.copyOf(tempObj, tempObj.length);
			for (int i = 0; i < tempObj.length; i++) {
				args[i] = tempObj[i];
			}
			args[tempObj.length] = curIndex + rowsPerPage;
			args[tempObj.length + 1] = curIndex;
		}
		// Object[] args = { curIndex + rowsPerPage, curIndex };
		// RowMapper<T> rowMapper =
		// BeanPropertyRowMapper.newInstance(entityClass);
		List<Map<String, Object>> list = jdbcTemplate.queryForList(sql.toString(), args);

		Map map = null;
		List<Object[]> dataList = new ArrayList<Object[]>();
		for (int i = 0; i < list.size(); i++) {
			map = list.get(i);
			Object[] obj = (Object[]) map.values().toArray();
			dataList.add(obj);
		}
		if(paramList == null ||paramList.size() == 0){
			pagination.maxRowCount = (this.count(pagination.queryString));

		}else{
			pagination.maxRowCount = (this.count(pagination.queryString, paramList.toArray()));
		}
		

		pagination.page = currPage;
		pagination.setData(dataList);
		pagination.setOldData(list);
		return pagination;

	}

	/**
	 * 返回easyui grid需要的分页数据集合
	 * 
	 * @param pagination
	 * @param prefix
	 * @return
	 */
	 
	public Map getPaginationMap(Pagination pagination, String prefix, List list) {
		pagination = this.getPagination(pagination, prefix, list);
		Map map = new HashMap();
	//	map.put("total", jdbcTemplate.queryForLong("select count(*) from (" + pagination.queryString + ")"));
		map.put("total", pagination.getMaxRowCount());
		map.put("rows", pagination.getOldData());
		return map;
	}

	/**
	 * 内部方法，拼装count语句时使用
	 * 
	 * @param where
	 * @return
	 */
	public long count(String queryString, Object[] args) {
		StringBuffer sql = new StringBuffer(" SELECT COUNT(*) FROM (" + queryString + ")");
		log.debug("count SQL=" + sql);
		return jdbcTemplate.queryForLong(sql.toString(), args);
	}
	
	/**
	 * 内部方法，拼装count语句时使用
	 * 
	 * @param where
	 * @return
	 */
	public long count(String queryString) {
		StringBuffer sql = new StringBuffer(" SELECT COUNT(*) FROM (" + queryString + ")");
		log.debug("count SQL=" + sql);
		return jdbcTemplate.queryForLong(sql.toString());
	}
	
/*	public int count(String sql, Object...params){
		List<Map<String, Object>> resultList = jdbcTemplate.queryForList(sql, params);
		return resultList != null && !resultList.isEmpty() ? resultList.size() : 0;
	}*/

	/**
	 * 内部方法，拼装count语句时使用
	 * 
	 * @param where
	 * @return
	 */
	private int count(Map<String, String> where) {
		StringBuffer sql = new StringBuffer(" SELECT COUNT(*) FROM " + SystemConstant.TBL
				+ entityClass.getSimpleName().replace(SystemConstant.TBL_SUFFIX, ""));
		if (where != null && where.size() > 0) {
			sql.append(" WHERE ");
			for (Map.Entry<String, String> me : where.entrySet()) {
				String columnName = me.getKey();
				String columnValue = me.getValue();
				sql.append(columnName).append(" ").append(columnValue).append(" AND "); // 没有考虑or的情况
			}
			int endIndex = sql.lastIndexOf("AND");
			if (endIndex > 0) {
				sql = new StringBuffer(sql.substring(0, endIndex));
			}
		}
		log.debug("SQL=" + sql);
		return jdbcTemplate.queryForInt(sql.toString());
	}

	/**
	 * 组装SQL
	 * 
	 * @param sqlFlag
	 * @return
	 */
	private String makeSql(String sqlFlag,List noDataList) {
		StringBuffer sql = new StringBuffer();
		Field[] entityFields = entityClass.getDeclaredFields();
		Field[] superFields = entityClass.getSuperclass().getDeclaredFields();
		Field[] fields = (Field[]) ArrayUtils.addAll(entityFields, superFields);
		if (sqlFlag.equals(SQL_INSERT)) {
			sql.append(" INSERT INTO " + SystemConstant.TBL
					+ entityClass.getSimpleName().replace(SystemConstant.TBL_SUFFIX, ""));
			sql.append("(");
			int t = 10000;
			for (int i = 0; fields != null && i < fields.length; i++) {
				fields[i].setAccessible(true); // 暴力反射
				String column = fields[i].getName();
				if (!column.equals("rec_id")) { // id 代表主键
					sql.append(column).append(",");
				}else{
					t = i;
				}
				
			}
			sql = sql.deleteCharAt(sql.length() - 1);
			sql.append(") VALUES (");
			for (int i = 0; fields != null && i < fields.length; i++) {
				if(i != t){
					sql.append("?,");
				}
			
			}
			sql = sql.deleteCharAt(sql.length() - 1);
			sql.append(")");
		} else if (sqlFlag.equals(SQL_UPDATE)) {
			sql.append(" UPDATE " + SystemConstant.TBL
					+ entityClass.getSimpleName().replace(SystemConstant.TBL_SUFFIX, "") + " SET ");
			for (int i = 0; fields != null && i < fields.length; i++) {
				fields[i].setAccessible(true); // 暴力反射
				String column = fields[i].getName();
				if (column.equals("rec_id")) { // id 代表主键
					continue;
				}
				if( ( noDataList == null || !noDataList.contains(column))){
					sql.append(column).append("=").append("?,");	
				}
				
			}
			sql = sql.deleteCharAt(sql.length() - 1);
			sql.append(" WHERE rec_id=?");
		} else if (sqlFlag.equals(SQL_DELETE)) {
			sql.append(" DELETE FROM " + SystemConstant.TBL
					+ entityClass.getSimpleName().replace(SystemConstant.TBL_SUFFIX, "") + " WHERE rec_id=?");
		}
	 
		return sql.toString();

	}

	/**
	 * 设置参数
	 * 
	 * @param entity
	 * @param sqlFlag
	 * @return
	 */
	private Object[] setArgs(T entity, String sqlFlag,List noDataList) {
		Field[] entityFields = entityClass.getDeclaredFields();
		Field[] superFields = entityClass.getSuperclass().getDeclaredFields();
		Field[] fields = (Field[]) ArrayUtils.addAll(entityFields, superFields);

		if (sqlFlag.equals(SQL_INSERT)) {
			Object[] args = new Object[fields.length];
			String temp = "";
			int t = 0;
			for (int i = 0; args != null && i < args.length; i++) {
				try {
					fields[i].setAccessible(true); // 暴力反射
					String fieldName = fields[i].getName();
					if (!fieldName.equals(SystemConstant.REC_ID) && ( noDataList == null || !noDataList.contains(fieldName))) {
						args[t] = fields[i].get(entity);
						temp = temp + "," + args[i];
						t = t +1;
					}
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		//	 log.debug("args[i]=========="+temp);
			return args;
		} else if (sqlFlag.equals(SQL_UPDATE)) {
			Object[] tempArr = new Object[fields.length];
			long rec_id = 0;
			String temp = "";
			boolean recBol = false;
			int y = 0;
			for (int i = 0; i < tempArr.length; i++) {
				try {
					fields[i].setAccessible(true); // 暴力反射
					String fieldName = fields[i].getName();
				//	log.debug("( !noDataList.contains(fieldName))=="+( noDataList == null || !noDataList.contains(fieldName)));
					if (!fieldName.equals(SystemConstant.REC_ID) && ( noDataList == null || !noDataList.contains(fieldName))) {
						if (recBol) {
							tempArr[y - 1] = fields[i].get(entity);
							temp = temp + "," + tempArr[i - 1];
						} else {
							tempArr[y] = fields[i].get(entity);
							temp = temp + "," + tempArr[i];
						}
						y++;

					} else if  ( noDataList == null || !noDataList.contains(fieldName)){
						rec_id = Long.parseLong(String.valueOf(fields[i].get(entity)));
						recBol = true;
						y++;
					
					}
				/*	log.debug("y==setArgs="+y);
					log.debug("tempArr=setArgs="+fieldName);*/
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
			
			Object[] args = new Object[y];
			System.arraycopy(tempArr, 0, args, 0, y); // 数组拷贝
			args[y - 1] = rec_id;
			return args;
		} else if (sqlFlag.equals(SQL_DELETE)) {
			Object[] args = new Object[1]; // 长度是1
			fields[0].setAccessible(true); // 暴力反射
			try {
				args[0] = fields[0].get(entity);
			} catch (Exception e) {
				e.printStackTrace();
			}
			return args;
		}
		return null;

	}

	/**
	 * 设置参数类型（写的不全，只是一些常用的）
	 * 
	 * @param entity
	 * @param sqlFlag
	 * @return
	 */
	private int[] setArgTypes(T entity, String sqlFlag,List noDataList) {
		Field[] entityFields = entityClass.getDeclaredFields();
		Field[] superFields = entityClass.getSuperclass().getDeclaredFields();
		Field[] fields = (Field[]) ArrayUtils.addAll(entityFields, superFields);
		if (sqlFlag.equals(SQL_INSERT)) {
			int[] argTypes = new int[fields.length];
			try {
				boolean temp = false;
				int t = 0;
				for (int i = 0; argTypes != null && i < argTypes.length; i++) {
					fields[i].setAccessible(true); // 暴力反射
					String fieldName = fields[i].getName();
			 
					if (!fieldName.equals(SystemConstant.REC_ID)&& ( noDataList == null || !noDataList.contains(fieldName))) {
						if (fields[i].get(entity) != null) {							 
							// log.debug(
							// "fields[i].get(entity).getClass().getName()=="
							// +fields[i].get(entity).getClass().getName());
							if (fields[i].get(entity).getClass().getName().equals("java.lang.String")) {
								argTypes[t] = Types.VARCHAR;
							} else if (fields[i].get(entity).getClass().getName().equals("java.lang.Double")) {
								argTypes[t] = Types.DECIMAL;
							} else if (fields[i].get(entity).getClass().getName().equals("java.lang.Integer")) {
								argTypes[t] = Types.INTEGER;
							} else if (fields[i].get(entity).getClass().getName().equals("java.util.Date")) {
								argTypes[t] = Types.DATE;
							} else if (fields[i].get(entity).getClass().getName().equals("java.lang.Float")) {
								argTypes[t] = Types.DECIMAL;
							} else if ((fields[i].get(entity).getClass().getName().equals("java.lang.Long"))) {
								argTypes[t] = Types.LONGVARCHAR;
							} else if (fields[i].get(entity).getClass().getName().equals("java.sql.Timestamp")) {
								argTypes[t] = Types.TIMESTAMP;
							}
							
						} else {
							argTypes[i] = Types.VARCHAR;
							
						}
						t = t + 1;
					}else{
						temp = true;
					}
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
			return argTypes;
		} else if (sqlFlag.equals(SQL_UPDATE)) {
			int[] tempArgTypes = new int[fields.length];
			int[] argTypes = new int[fields.length];
			try {
				boolean recBol = false;
				int y = 0;
				for (int i = 0; tempArgTypes != null && i < tempArgTypes.length; i++) {
					fields[i].setAccessible(true); // 暴力反射

					String fieldName = fields[i].getName();
					if(  noDataList == null || !noDataList.contains(fieldName)){
						if (fields[i].get(entity) != null) {
							// log.debug(
							// "fields[i].get(entity).getClass().getName()=="
							// +fields[i].get(entity).getClass().getName());
							if (fields[i].get(entity).getClass().getName().equals("java.lang.String")) {
								if (recBol) {
									tempArgTypes[y - 1] = Types.VARCHAR;
								} else {
									tempArgTypes[y] = Types.VARCHAR;
								}
								y++;

							} else if (fields[i].get(entity).getClass().getName().equals("java.lang.Double")) {
								if (recBol) {
									tempArgTypes[y - 1] = Types.DECIMAL;
								} else {
									tempArgTypes[y] = Types.DECIMAL;
								}
								y++;

							} else if (fields[i].get(entity).getClass().getName().equals("java.lang.Integer")) {
								if (recBol) {
									tempArgTypes[y - 1] = Types.INTEGER;
								} else {
									tempArgTypes[y] = Types.INTEGER;
								}
								y++;
							} else if (fields[i].get(entity).getClass().getName().equals("java.util.Date")) {
								if (recBol) {
									tempArgTypes[y - 1] = Types.DATE;
								} else {
									tempArgTypes[y] = Types.DATE;
								}y++;

							} else if (fields[i].get(entity).getClass().getName().equals("java.sql.Timestamp")) {
								if (recBol) {
									tempArgTypes[y - 1] = Types.TIMESTAMP;
								} else {
									tempArgTypes[y] = Types.TIMESTAMP;
								}y++;

							} else if (fields[i].get(entity).getClass().getName().equals("java.lang.Float")) {
								if (recBol) {
									tempArgTypes[y - 1] = Types.DECIMAL;
								} else {
									tempArgTypes[y] = Types.DECIMAL;
								}y++;
							} else if (!fieldName.equals(SystemConstant.REC_ID)
									&& (fields[i].get(entity).getClass().getName().equals("java.lang.Long"))) {
								if (recBol) {
									tempArgTypes[y - 1] = Types.LONGVARCHAR;
								} else {
									tempArgTypes[y] = Types.LONGVARCHAR;
								}y++;

							} else if (fieldName.equals(SystemConstant.REC_ID)) {
								recBol = true;
								y++;
							}
						} else {
							argTypes[i] = Types.VARCHAR;
							y++;
						}
					}
				
				//	log.debug("y==setArgTypes="+y);
				//	log.debug("fieldName="+fieldName);
				}
				argTypes = new int[y];
				System.arraycopy(tempArgTypes, 0, argTypes, 0, y); // 数组拷贝
				argTypes[y - 1] = Types.LONGVARCHAR;

			} catch (Exception e) {
				e.printStackTrace();
			}
			return argTypes;

		} else if (sqlFlag.equals(SQL_DELETE)) {
			int[] argTypes = new int[1]; // 长度是1
			try {
				fields[0].setAccessible(true); // 暴力反射
				if (fields[0].get(entity).getClass().getName().equals("java.lang.String")) {
					argTypes[0] = Types.VARCHAR;
				} else if (fields[0].get(entity).getClass().getName().equals("java.lang.Integer")) {
					argTypes[0] = Types.INTEGER;
				}

			} catch (Exception e) {
				e.printStackTrace();
			}
			return argTypes;
		}
		return null;
	}

	/**
	 * 查询数据集合
	 * @param sql
	 * @return
	 */
	@Deprecated
	public List queryForList(String sql) {
		log.debug("queryForList sql : " + sql);
		return this.jdbcTemplate.queryForList(sql);
	}
 

	/**
	 * 查询数据集合
	 * @param sql 查询sql，使用预编译sql写法，不要将查询条件直接写入sql中
	 * @return List<Map>
	 */
	public List queryForList(String sql, List paramList) {
		Object[] objects = null;
		if(paramList != null && !paramList.isEmpty()){
			objects = paramList.toArray();
		}
		return queryForListMap(sql, objects);
	}
	
	public List<Map<String, Object>> queryForListMap(String sql, Object...objects) {
		log.debug("queryForList sql : " + sql);
		if(objects != null && objects.length  > 0){
			return this.jdbcTemplate.queryForList(sql, objects);
		}else{
			return this.jdbcTemplate.queryForList(sql);
		}
	}

	/**
	 * 查询数据集合,返回MAP,此方法性能较差，不建议使用，建议使用queryForList
	 * @param sql
	 * @return Map
	 */
	@Deprecated
	public Map queryForMap(String sql) {
		log.debug("queryForMap sql : " + sql);
		return this.jdbcTemplate.queryForMap(sql);
	}

	/**
	 * 查询数据集合,返回MAP,此方法性能较差，不建议使用，建议使用queryForList
	 * @param sql
	 * @return Map
	 */
	@Deprecated
	public Map queryForMap(String sql, List list) {
		log.debug("queryForMap sql : " + sql);
		return this.jdbcTemplate.queryForMap(sql, list.toArray());
	}
	
	/**
	 * 调用有返回值的存过
	 * @param proName 存过名称
	 * @param oracleTypes 出参类型
	 * @param inParams 入参
	 * @return key是出参下标，value是返回值
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public Map<String, Object> queryProcedure(final String proName, final int[] oracleTypes, final Object...inParams){
		if(oracleTypes == null || oracleTypes.length == 0){
			return null;
		}
		Map<String, Object> result = (Map<String, Object>) jdbcTemplate.execute(new CallableStatementCreator() {
			@Override
			public CallableStatement createCallableStatement(Connection con) throws SQLException {
				StringBuffer sql = new StringBuffer();
				sql.append("{call ").append(proName).append("(");
				/*根据入参数量，组装占位符个数*/
				if(inParams != null && inParams.length > 0){
					for(int i=0;i<inParams.length;i++){
						sql.append("?,");
					}
				}
				/*根据出参数量，组装占位符个数*/
				for(int i=0; i<oracleTypes.length; i++){
					sql.append("?,");
				}
				String finalSql = sql.substring(0, sql.lastIndexOf(",")) + ")}";
				log.debug("queryForMap sql：{}", finalSql);
				CallableStatement cs = con.prepareCall(finalSql);
				int i = 1;
				/*入参赋值*/
				if(inParams != null && inParams.length > 0){
					for(Object param : inParams){
						cs.setObject(i++, param);
					}
				}
				/*出参赋类型*/
				for(int oracleType : oracleTypes){
					cs.registerOutParameter(i++, oracleType);
				}
				return cs;
			}
		}, new CallableStatementCallback() {
			@Override
			public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
				Map<String, Object> resultMap = new LinkedHashMap<>();
				cs.execute();
				int i = inParams == null || inParams.length == 0 ? 1 : inParams.length + 1;
				for(int j=0;j<oracleTypes.length;j++){
					resultMap.put(String.valueOf(i), cs.getObject(i++));
				}
				return resultMap;
			}
		});
		return result;
	}
	
	/**
	 * 调用有结果集的存过
	 * @param pName 存过名称
	 * @param params 入参
	 * @return 按照存过查询字段顺序，返回集合；key为字段名称，value为字段值
	 */
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public List<Map<String, Object>> queryProcedure(final String pName, final Object...params){
		List<Map<String, Object>> result = (List<Map<String, Object>>) jdbcTemplate.execute(new CallableStatementCreator(){
			@Override
			public CallableStatement createCallableStatement(Connection con) throws SQLException {
				StringBuffer sql = new StringBuffer();
				sql.append("{call ").append(pName).append("(");
				/*根据入参数量，组装占位符个数*/
				if(params != null && params.length > 0){
					for(int i=0;i<params.length;i++){
						sql.append("?,");
					}
				}
				/*最后一个占位符为结果集*/
				sql.append("?)}");
				CallableStatement cs = con.prepareCall(sql.toString());
				int i = 1;
				if(params != null && params.length > 0){
					for(Object param : params){
						cs.setObject(i++, param);
					}
				}
				cs.registerOutParameter(i, OracleTypes.CURSOR);
				return cs;
			}
		}, new CallableStatementCallback(){
			@Override
			public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
				List<Map<String, Object>> resultMap = new ArrayList<>();
				cs.execute();
				ResultSet rs = (ResultSet) cs.getObject(params.length + 1);
				if(rs != null){
					ResultSetMetaData data = rs.getMetaData();
					int colums = data.getColumnCount();
					while(rs.next()){
						/*有序集合，按照存过结果，有序排列列名*/
						Map<String, Object> rowMap = new LinkedHashMap<>();
						for(int i=1;i<=colums;i++){
							rowMap.put(data.getColumnName(i), rs.getObject(i));
						}
						resultMap.add(rowMap);
					}
				}
				return resultMap;
			}
		});
		return result;
	}
}
